<?php
class FrontProduct
{
	public function __construct()
	{
		
	}
	
	public function getMaxRateDate()
	{
		$sql = "SELECT MAX(rate_date) AS MAXRATEDATE
				 FROM tbl_rates";
		$qry = DB::fetchRow($sql);
		
		$rate_date = $qry['MAXRATEDATE'];
		
		return $rate_date;
	}
	
	public function getProduct($limit='')
	{
		DB::exec("SET NAMES 'utf8'");
		
		$sql2 = "SELECT P.discount,P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.product_cat_id, P.product_subcat_id, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename
				FROM tbl_products P
                                inner join tbl_homepageproducts H on H.productid=P.productid
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				WHERE P.status = 1 and P.quantity>0   order by H.sortorder " /*. $limit*/;
					
		$item_arr = DB::fetchAll($sql2);
		
		return $item_arr;
	}
	
	public function getProductById($productid)
	{
		DB::exec("SET NAMES 'utf8'");
		
		$sql = "SELECT P.discount,P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.product_cat_id, P.product_subcat_id, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename, C.cat_desc
				FROM tbl_products P
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				LEFT JOIN tbl_category C ON C.cat_id = P.product_cat_id
				WHERE P.productid = '" . $productid . "'  
				AND P.status = 1";				
		
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	
	public function addUserClick($itemcode)
	{
		$sql1 = "SELECT user_clicks
				FROM tbl_products
				WHERE item_code = '" . $itemcode . "' ";
		
		$rs1 = DB::fetchRow($sql1);
		$user_clicks = $rs1['user_clicks'];
		
		$sql2 = "UPDATE tbl_products
				 SET user_clicks = " . ($user_clicks + 1) . "
				 WHERE item_code = '" . $itemcode . "' ";
		DB::exec($sql2);
	}
	
	public function getAllProducts()
	{
		$sql = "SELECT P.item_code,P.discount, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.product_cat_id, P.product_subcat_id, P.created_date, P.status
				FROM tbl_products P
                              
				WHERE P.status = 1 and P.quantity>0 ";
				
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	
	public function getMostViewedProducts()
	{
		DB::exec("SET NAMES 'utf8'");
		
		$max_rate_date = $this->getMaxRateDate();
		
		$sql = "SELECT P.discount,P.item_code, P.product_title, P.product_dsc_en, P.product_dsc_sin, P.product_dsc_tamil, P.product_price_lkr, P.quantity, P.product_cat_id, P.product_subcat_id, P.created_date, P.status, PI.filename, R.rate
				FROM tbl_products P
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				LEFT JOIN tbl_rates R ON  R.rate_date = '" . $max_rate_date . "'
				WHERE P.status = 1
				AND P.user_clicks >= 10				
				ORDER BY RAND()
				LIMIT 0, 3";	
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	
	public function getProductsByCategory($cat_id, $limit,$where)
	{
		DB::exec("SET NAMES 'utf8'");
		
		$sqlbycat = "SELECT P.discount,P.product_cat_id , P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename
				FROM tbl_products P
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				WHERE P.product_cat_id = " . $cat_id . "
				AND status = 1 and P.quantity>0  ". $where . $limit;
		$item_arr = DB::fetchAll($sqlbycat);
		
		return $item_arr;
	}
	
	public function getProductsBySubCategory($cat_id, $limit,$where)
	{
		DB::exec("SET NAMES 'utf8'");
		
		$sql = "SELECT P.discount,P.product_cat_id ,P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename
				FROM tbl_products P
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				WHERE P.product_subcat_id = " . $cat_id . "
				AND status = 1 and P.quantity>0 " . $where . $limit;
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	public function getProductsBySearch($request)
	{
		DB::exec("SET NAMES 'utf8'");

		$sql = "SELECT P.discount,P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename
				FROM tbl_products P
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				WHERE status=1 and P.quantity>0 and searchstr like '%".$request["searchkeyword"]."%' ";
                //echo $sql;
		$item_arr = DB::fetchAll($sql);

		return $item_arr;
	}
	public function getNewArrivals()
	{
		DB::exec("SET NAMES 'utf8'");
		
		$max_rate_date = $this->getMaxRateDate();
		
		$sql = "SELECT P.item_code, P.product_title, P.product_dsc_en, P.product_dsc_sin, P.product_dsc_tamil, P.product_price_lkr, P.quantity, P.created_date, P.status, PI.filename, R.rate
				FROM tbl_products P
				LEFT JOIN tbl_productimage PI ON PI.productid = P.item_code
				LEFT JOIN tbl_rates R ON  R.rate_date = '" . $max_rate_date . "'
				WHERE P.new_arrival = 1
				AND status = 1
				ORDER BY RAND()
				LIMIT 0, 1";
		
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	
	public function validateQty($productid)
	{
		$sql = "SELECT quantity
				FROM tbl_products
				WHERE productid = '" . $productid . "' ";
		
		$qty = DB::fetchRow($sql);
		
		return $qty;
	}

        public function getProductsByprintcartridgesearch($request)
	{
		DB::exec("SET NAMES 'utf8'");

		$sql = "SELECT P.discount,P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename
				FROM tbl_products P inner join tbl_printerfinder PF on P.productid=PF.productid
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				WHERE status=1 and PF.cartridgeno='".$request["cartridgeno"]."'  ";
             //   echo $sql;
		$item_arr = DB::fetchAll($sql);

		return $item_arr;
	}

        public function getProductsByprintmodelsearch($request)
	{
		DB::exec("SET NAMES 'utf8'");

		$sql = "SELECT P.discount,P.productid, P.item_code, P.product_title, P.product_dsc_en, P.product_price_lkr, P.quantity, P.created_date, P.status, IFNULL(PI.filename,'NoImage.jpg') as filename
				FROM tbl_products P inner join tbl_printerfinder PF on P.productid=PF.productid
				LEFT JOIN tbl_productimage PI ON PI.productid = P.productid
				WHERE status=1 and PF.modelno='".$request["modelno"]."'  ";
               // echo $sql;
		$item_arr = DB::fetchAll($sql);
		return $item_arr;
	}
         public function  getbrandsforprintesearch()
	{
		DB::exec("SET NAMES 'utf8'");

		$sql = "SELECT distinct brand as brand from tbl_printerfinder where brand<>''";
               // echo $sql;
		$brandarr = DB::fetchAll($sql);
                return $brandarr;

         }
          public function  getprodbybrands($request)
	{
		DB::exec("SET NAMES 'utf8'");
                if($request["brand"]==""){
                    return false;
                }
		$sql = "SELECT productid,item_code   from tbl_printerfinder where brand='".$request["brand"]."'";
             //   echo $sql;
		$brandarr = DB::fetchAll($sql);
             //   print_r($brandarr);
                return $brandarr;

         }
         public function  getFeature($productid)
	{
		DB::exec("SET NAMES 'utf8'");
		$sql = "SELECT structkey,structvalue   from tbl_productfeatures where productid='".$productid."'";
             //   echo $sql;
		$brandarr = DB::fetchAll($sql);
             //   print_r($brandarr);
                return $brandarr;

         }



}
?>